import os
import re
import logging
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
from collections import Counter
import jieba
from wordcloud import WordCloud
from matplotlib.font_manager import FontProperties
import matplotlib as mpl
import sys

# 导入自定义模块
sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))
from app.interface_xue.excelhelper_xue import excelhelper_xue

class ProductCategoryAnalyzer:
    """
    产品类别分析器 - 支持任意产品类别的亚马逊数据分析
    使用ExcelHelper类操作数据，适配修改后的MySQL字段
    """
    
    def __init__(self, category="home decor", report_dir=None):
        """
        初始化产品分析器
        
        参数:
            category: 产品类别名称，默认为"home decor"
            report_dir: 报告保存目录，默认为category名称下的report子目录
        """
        self.category = category
        self.category_slug = category.lower().replace(' ', '_')
        self.excel_helper = excelhelper_xue()  # 初始化ExcelHelper实例
        
        # 设置报告目录
        base_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), 'media', 'reports')
        os.makedirs(base_dir, exist_ok=True)
        
        if report_dir is None:
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            self.report_dir = os.path.join(base_dir, f"{self.category_slug}_{timestamp}")
        else:
            self.report_dir = os.path.join(base_dir, report_dir)
            
        # 创建报告目录
        if not os.path.exists(self.report_dir):
            os.makedirs(self.report_dir)
            
        # 日志初始化
        self.log_file = os.path.join(self.report_dir, f"{self.category_slug}_analysis.log")
        self.setup_logging()
        
        # 设置中文字体
        self.set_chinese_font()
    
    def setup_logging(self):
        """设置日志系统"""
        self.logger = logging.getLogger(f"{self.category_slug}_analyzer")
        self.logger.setLevel(logging.INFO)
        
        # 清除已有的处理器
        if self.logger.handlers:
            self.logger.handlers = []
        
        # 文件处理器
        file_handler = logging.FileHandler(self.log_file, encoding='utf-8')
        file_format = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
        file_handler.setFormatter(file_format)
        self.logger.addHandler(file_handler)
        
        # 控制台处理器
        console_handler = logging.StreamHandler()
        console_handler.setFormatter(file_format)
        self.logger.addHandler(console_handler)
        
        self.logger.info(f"===== {self.category}产品分析开始 =====")
        self.logger.info(f"报告将保存在: {self.report_dir}")
    
    def set_chinese_font(self):
        """设置matplotlib中文字体支持"""
        try:
            plt.rcParams['font.sans-serif'] = ['SimHei', 'Arial Unicode MS', 'DejaVu Sans', 'Bitstream Vera Sans']
            plt.rcParams['axes.unicode_minus'] = False  # 解决负号显示问题
            plt.rcParams['figure.figsize'] = (12, 8)  # 设置默认图表大小
            
            # 额外尝试字体路径
            font_paths = [
                '/usr/share/fonts/truetype/arphic/',  # Linux字体路径
                'C:\\Windows\\Fonts\\',  # Windows字体路径
                '/System/Library/Fonts/',  # macOS字体路径
                os.path.join(os.path.dirname(__file__), 'fonts/')  # 当前目录下的fonts目录
            ]
            
            # 遍历可能的字体文件
            for font_path in font_paths:
                for font_file in ['simhei.ttf', 'SimHei.ttf', 'simsun.ttc', 'SimSun.ttc']:
                    full_path = os.path.join(font_path, font_file)
                    if os.path.exists(full_path):
                        mpl.font_manager.fontManager.addfont(full_path)
                        self.logger.info(f"加载字体: {full_path}")
                        break
                
            self.logger.info("中文字体设置完成")
        except Exception as e:
            self.logger.warning(f"设置中文字体时出错: {str(e)}")
    
    def get_data_from_excel(self, days=30, limit=1000):
        """
        使用ExcelHelper获取产品数据
        
        参数:
            days: 获取最近多少天的数据
            limit: 最多获取多少条记录
        """
        try:
            # 计算日期范围
            end_date = datetime.now()
            start_date = end_date - timedelta(days=days)
            
            # 使用excel_helper查询数据 - 这里使用更新后的字段名
            sql = f"""
            SELECT productid AS asin, title, price, rating, reviews_count AS reviews, maincategory AS category, 
                   opdate AS date, seller, mainpic AS image_url, memo AS description
            FROM app_product_xue
            WHERE (keywords LIKE '%{self.category}%' OR title LIKE '%{self.category}%')
            AND opdate >= '{start_date.strftime('%Y-%m-%d')}'
            ORDER BY opdate DESC
            LIMIT {limit}
            """
            
            # 使用ExcelHelper执行查询
            result = self.excel_helper.query_one(sql)
            
            # 检查结果是否为列表
            if not result or not isinstance(result, list):
                self.logger.info("查询没有返回有效数据")
                return pd.DataFrame()
                
            # 将结果转换为DataFrame
            df = pd.DataFrame(result)
            
            # 转换数据类型
            if 'price' in df.columns:
                df['price'] = pd.to_numeric(df['price'], errors='coerce')
            if 'rating' in df.columns:
                df['rating'] = pd.to_numeric(df['rating'], errors='coerce')
            if 'reviews' in df.columns:
                df['reviews'] = pd.to_numeric(df['reviews'], errors='coerce')
            
            self.logger.info(f"从数据库获取了 {len(df)} 条{self.category}产品记录")
            return df
        
        except Exception as e:
            self.logger.error(f"获取数据时出错: {str(e)}")
            # 返回空DataFrame
            return pd.DataFrame()
    
    def analyze_price_distribution(self, df):
        """分析价格分布"""
        if df.empty or 'price' not in df.columns:
            self.logger.info("数据为空或不包含价格列，无法分析价格分布")
            return
        
        try:
            # 确保价格是数值类型
            df['price'] = pd.to_numeric(df['price'], errors='coerce')
            df = df.dropna(subset=['price'])
            
            # 基本价格统计
            price_stats = df['price'].describe()
            self.logger.info(f"\n## {self.category}价格分析")
            self.logger.info(f"价格统计:\n{price_stats.to_string()}")
            
            # 绘制价格分布直方图
            plt.figure(figsize=(10, 6))
            plt.hist(df['price'], bins=20, alpha=0.7, color='skyblue')
            plt.title(f"{self.category}产品价格分布")
            plt.xlabel("价格")
            plt.ylabel("产品数量")
            plt.grid(True, alpha=0.3)
            
            # 添加均值和中位数线
            plt.axvline(price_stats['mean'], color='red', linestyle='dashed', linewidth=1, label=f"均值: ${price_stats['mean']:.2f}")
            plt.axvline(price_stats['50%'], color='green', linestyle='dashed', linewidth=1, label=f"中位数: ${price_stats['50%']:.2f}")
            plt.legend()
            
            # 保存图表
            price_chart_path = os.path.join(self.report_dir, f"{self.category_slug}_price_distribution.png")
            plt.savefig(price_chart_path)
            plt.close()
            
            self.logger.info(f"价格分布图已保存至: {price_chart_path}")
            
            # 划分价格区间并计算各区间产品数量
            price_ranges = [0, 10, 20, 30, 50, 100, 200, float('inf')]
            labels = ['<$10', '$10-20', '$20-30', '$30-50', '$50-100', '$100-200', '>$200']
            df['price_range'] = pd.cut(df['price'], bins=price_ranges, labels=labels, right=False)
            price_range_counts = df['price_range'].value_counts().sort_index()
            
            self.logger.info(f"\n价格区间分布:\n{price_range_counts.to_string()}")
            
            # 绘制价格区间柱状图
            plt.figure(figsize=(12, 6))
            price_range_counts.plot(kind='bar', color='skyblue')
            plt.title(f"{self.category}产品价格区间分布")
            plt.xlabel("价格区间")
            plt.ylabel("产品数量")
            plt.grid(True, alpha=0.3, axis='y')
            
            # 添加数值标签
            for i, v in enumerate(price_range_counts):
                plt.text(i, v + 0.5, str(v), ha='center')
            
            # 保存图表
            price_range_chart_path = os.path.join(self.report_dir, f"{self.category_slug}_price_ranges.png")
            plt.savefig(price_range_chart_path)
            plt.close()
            
            self.logger.info(f"价格区间分布图已保存至: {price_range_chart_path}")
            
        except Exception as e:
            self.logger.error(f"分析价格分布时出错: {str(e)}")
    
    def analyze_ratings(self, df):
        """分析评分分布"""
        if df.empty or 'rating' not in df.columns:
            self.logger.info("数据为空或不包含评分列，无法分析评分分布")
            return
        
        try:
            # 移除可能的非数值评分
            df['rating'] = pd.to_numeric(df['rating'], errors='coerce')
            df_ratings = df.dropna(subset=['rating'])
            
            # 基本评分统计
            rating_stats = df_ratings['rating'].describe()
            self.logger.info(f"\n## {self.category}评分分析")
            self.logger.info(f"评分统计:\n{rating_stats.to_string()}")
            
            # 统计各评分数量
            rating_counts = df_ratings['rating'].value_counts().sort_index()
            
            # 绘制评分分布柱状图
            plt.figure(figsize=(10, 6))
            rating_counts.plot(kind='bar', color='lightgreen')
            plt.title(f"{self.category}产品评分分布")
            plt.xlabel("评分")
            plt.ylabel("产品数量")
            plt.grid(True, alpha=0.3, axis='y')
            
            # 添加数值标签
            for i, v in enumerate(rating_counts):
                plt.text(i, v + 0.5, str(v), ha='center')
            
            # 保存图表
            rating_chart_path = os.path.join(self.report_dir, f"{self.category_slug}_rating_distribution.png")
            plt.savefig(rating_chart_path)
            plt.close()
            
            self.logger.info(f"评分分布图已保存至: {rating_chart_path}")
            
            # 分析评分与价格的关系
            plt.figure(figsize=(10, 6))
            plt.scatter(df_ratings['price'], df_ratings['rating'], alpha=0.5)
            plt.title(f"{self.category}产品价格与评分关系")
            plt.xlabel("价格")
            plt.ylabel("评分")
            plt.grid(True, alpha=0.3)
            
            # 添加趋势线
            if len(df_ratings) > 1:
                z = np.polyfit(df_ratings['price'], df_ratings['rating'], 1)
                p = np.poly1d(z)
                plt.plot(df_ratings['price'], p(df_ratings['price']), "r--", alpha=0.8)
            
            # 保存图表
            rating_price_chart_path = os.path.join(self.report_dir, f"{self.category_slug}_rating_price_relation.png")
            plt.savefig(rating_price_chart_path)
            plt.close()
            
            self.logger.info(f"评分与价格关系图已保存至: {rating_price_chart_path}")
            
        except Exception as e:
            self.logger.error(f"分析评分分布时出错: {str(e)}")
    
    def analyze_titles(self, df):
        """分析产品标题关键词"""
        if df.empty or 'title' not in df.columns:
            self.logger.info("数据为空或不包含标题列，无法分析标题关键词")
            return
        
        try:
            # 获取所有标题
            titles = df['title'].fillna('').astype(str).tolist()
            
            # 分词处理
            all_words = []
            for title in titles:
                # 移除特殊字符
                title = re.sub(r'[^\w\s]', ' ', title)
                
                # 检测是否包含中文
                if any('\u4e00' <= ch <= '\u9fff' for ch in title):
                    # 中文分词
                    words = jieba.lcut(title)
                else:
                    # 英文分词
                    words = title.lower().split()
                
                all_words.extend(words)
            
            # 移除停用词
            english_stopwords = {'the', 'a', 'an', 'and', 'or', 'but', 'is', 'are', 'in', 'to', 'for', 'with', 'on', 'of', 'at'}
            chinese_stopwords = {'的', '了', '和', '与', '或', '是', '在', '有', '中', '也'}
            stopwords = english_stopwords.union(chinese_stopwords)
            
            filtered_words = [word for word in all_words if word.lower() not in stopwords and len(word) > 1]
            
            # 计算词频
            word_counter = Counter(filtered_words)
            top_words = word_counter.most_common(50)
            
            self.logger.info(f"\n## {self.category}产品标题关键词分析")
            self.logger.info(f"前50个关键词:\n{top_words}")
            
            # 生成词云
            wordcloud = WordCloud(
                width=1000, 
                height=600, 
                background_color='white',
                max_words=200,
                contour_width=3,
                contour_color='steelblue'
            ).generate(' '.join(filtered_words))
            
            plt.figure(figsize=(16, 10))
            plt.imshow(wordcloud, interpolation='bilinear')
            plt.axis('off')
            plt.title(f"{self.category}产品标题关键词云")
            
            # 保存词云图
            wordcloud_path = os.path.join(self.report_dir, f"{self.category_slug}_title_wordcloud.png")
            plt.savefig(wordcloud_path)
            plt.close()
            
            self.logger.info(f"标题关键词云已保存至: {wordcloud_path}")
            
            # 绘制前20个关键词柱状图
            top20_words = word_counter.most_common(20)
            words, counts = zip(*top20_words)
            
            plt.figure(figsize=(12, 8))
            plt.barh(range(len(words)), counts, align='center', color='skyblue')
            plt.yticks(range(len(words)), words)
            plt.xlabel('出现次数')
            plt.title(f"{self.category}产品标题热门关键词")
            plt.tight_layout()
            
            # 保存柱状图
            top_words_chart_path = os.path.join(self.report_dir, f"{self.category_slug}_top_keywords.png")
            plt.savefig(top_words_chart_path)
            plt.close()
            
            self.logger.info(f"热门关键词图已保存至: {top_words_chart_path}")
            
        except Exception as e:
            self.logger.error(f"分析标题关键词时出错: {str(e)}")
    
    def analyze_sentiment(self, df):
        """分析评论情感倾向"""
        if df.empty or 'description' not in df.columns:
            self.logger.info("数据为空或不包含描述列，无法进行情感分析")
            return
            
        try:
            try:
                from textblob import TextBlob
            except ImportError:
                self.logger.warning("无法进行情感分析，需要安装textblob库")
                return
                
            descriptions = df['description'].fillna('').astype(str).tolist()
            sentiments = []
            
            # 分析每个描述的情感
            for desc in descriptions:
                if not desc or len(desc) < 10:
                    continue
                    
                # 使用TextBlob进行情感分析
                blob = TextBlob(desc)
                sentiment = blob.sentiment.polarity  # -1到1之间的值，负值表示负面情感，正值表示正面情感
                sentiments.append(sentiment)
            
            if not sentiments:
                self.logger.info("没有足够的描述数据进行情感分析")
                return
                
            # 绘制情感分布直方图
            plt.figure(figsize=(10, 6))
            plt.hist(sentiments, bins=20, alpha=0.7, color='purple')
            plt.title(f"{self.category}产品描述情感分析")
            plt.xlabel("情感倾向 (-1: 极负面, 1: 极正面)")
            plt.ylabel("数量")
            plt.grid(True, alpha=0.3)
            
            # 保存图表
            sentiment_chart_path = os.path.join(self.report_dir, f"{self.category_slug}_sentiment_analysis.png")
            plt.savefig(sentiment_chart_path)
            plt.close()
            
            self.logger.info(f"情感分析图已保存至: {sentiment_chart_path}")
            
            # 计算平均情感得分
            avg_sentiment = sum(sentiments) / len(sentiments)
            self.logger.info(f"平均情感得分: {avg_sentiment:.4f} (-1到1之间, 越高越正面)")
            
        except Exception as e:
            self.logger.error(f"进行情感分析时出错: {str(e)}")
    
    def generate_pdf_report(self):
        """生成PDF报告"""
        try:
            from reportlab.lib.pagesizes import letter
            from reportlab.pdfgen import canvas
            from reportlab.lib import colors
            from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Image, Table, TableStyle
            from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
            
            pdf_path = os.path.join(self.report_dir, f"{self.category_slug}_report.pdf")
            doc = SimpleDocTemplate(pdf_path, pagesize=letter)
            styles = getSampleStyleSheet()
            
            # 创建标题样式
            title_style = ParagraphStyle(
                'Title',
                parent=styles['Title'],
                fontSize=16,
                spaceAfter=12
            )
            
            # 创建内容元素列表
            elements = []
            
            # 添加标题
            elements.append(Paragraph(f"{self.category}产品分析报告", title_style))
            elements.append(Spacer(1, 12))
            
            # 添加生成时间
            elements.append(Paragraph(f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}", styles["Normal"]))
            elements.append(Spacer(1, 12))
            
            # 添加图表
            for img_file in os.listdir(self.report_dir):
                if img_file.endswith('.png'):
                    img_path = os.path.join(self.report_dir, img_file)
                    # 提取图表名称
                    img_name = img_file.replace(f"{self.category_slug}_", "").replace(".png", "").replace("_", " ").title()
                    
                    # 添加图表标题
                    elements.append(Paragraph(img_name, styles["Heading2"]))
                    elements.append(Spacer(1, 6))
                    
                    # 添加图片
                    img = Image(img_path, width=450, height=300)
                    elements.append(img)
                    elements.append(Spacer(1, 12))
            
            # 生成PDF
            doc.build(elements)
            self.logger.info(f"PDF报告已生成: {pdf_path}")
            return pdf_path
        except ImportError:
            self.logger.warning("无法生成PDF报告，需要安装reportlab库")
            return None
        except Exception as e:
            self.logger.error(f"生成PDF报告时出错: {str(e)}")
            return None
    
    def generate_excel_report(self, df):
        """生成Excel报告"""
        try:
            import openpyxl
            from openpyxl.styles import Font, Alignment, PatternFill
            from openpyxl.utils import get_column_letter
            
            excel_path = os.path.join(self.report_dir, f"{self.category_slug}_data.xlsx")
            os.makedirs(excel_path, exist_ok=True)
            # 创建工作簿和工作表
            wb = openpyxl.Workbook()
            ws = wb.active
            ws.title = "产品数据"
            
            # 添加标题行
            headers = ["ASIN", "产品标题", "价格", "评分", "评论数", "分类", "卖家", "日期"]
            for col_num, header in enumerate(headers, 1):
                cell = ws.cell(row=1, column=col_num)
                cell.value = header
                cell.font = Font(bold=True)
                cell.alignment = Alignment(horizontal='center')
                cell.fill = PatternFill(start_color="ADD8E6", end_color="ADD8E6", fill_type="solid")
            
            # 添加数据行
            cols = ['asin', 'title', 'price', 'rating', 'reviews', 'category', 'seller', 'date']
            
            for row_num, index in enumerate(df.index, 2):
                for col_num, col in enumerate(cols, 1):
                    if col in df.columns:
                        cell = ws.cell(row=row_num, column=col_num)
                        value = df.loc[index, col]
                        if pd.notna(value):
                            cell.value = value
                        
                        # 设置单元格格式
                        if col == 'price' and pd.notna(value):
                            cell.number_format = '"$"#,##0.00'
                        elif col == 'rating' and pd.notna(value):
                            cell.number_format = '0.0'
                        elif col == 'reviews' and pd.notna(value):
                            cell.number_format = '#,##0'
                        elif col == 'date' and pd.notna(value):
                            cell.number_format = 'yyyy-mm-dd'
            
            # 调整列宽
            for col_num in range(1, len(headers) + 1):
                col_letter = get_column_letter(col_num)
                if col_num == 2:  # 标题列
                    ws.column_dimensions[col_letter].width = 50
                else:
                    ws.column_dimensions[col_letter].width = 15
            
            # 添加统计信息工作表
            ws_stats = wb.create_sheet(title="统计分析")
            
            # 添加统计数据
            stats_data = [
                ["产品数量", len(df)],
                ["平均价格", df['price'].mean() if 'price' in df.columns else None],
                ["价格中位数", df['price'].median() if 'price' in df.columns else None],
                ["最低价格", df['price'].min() if 'price' in df.columns else None],
                ["最高价格", df['price'].max() if 'price' in df.columns else None],
                ["平均评分", df['rating'].mean() if 'rating' in df.columns else None],
                ["平均评论数", df['reviews'].mean() if 'reviews' in df.columns else None],
            ]
            
            for row_num, (label, value) in enumerate(stats_data, 1):
                # 标签
                cell = ws_stats.cell(row=row_num, column=1)
                cell.value = label
                cell.font = Font(bold=True)
                
                # 值
                cell = ws_stats.cell(row=row_num, column=2)
                if value is not None:
                    if isinstance(value, float):
                        cell.value = round(value, 2)
                    else:
                        cell.value = value
            
            # 调整统计表列宽
            ws_stats.column_dimensions['A'].width = 20
            ws_stats.column_dimensions['B'].width = 15
            
            # 保存Excel文件
            wb.save(excel_path)
            self.logger.info(f"Excel报告已生成: {excel_path}")
            return excel_path
        except ImportError:
            self.logger.warning("无法生成Excel报告，需要安装openpyxl库")
            return None
        except Exception as e:
            self.logger.error(f"生成Excel报告时出错: {str(e)}")
            return None
    
    def generate_report(self, days=30, limit=1000):
        """生成完整分析报告"""
        self.logger.info(f"开始生成{self.category}产品分析报告...")
        
        # 从ExcelHelper获取数据
        df = self.get_data_from_excel(days, limit)
        
        if df.empty:
            self.logger.info("未获取到数据，报告生成失败")
            return False
        
        # 执行各项分析
        self.analyze_price_distribution(df)
        self.analyze_ratings(df)
        self.analyze_titles(df)
        self.analyze_sentiment(df)
        
        # 汇总结果
        self.logger.info(f"\n## {self.category}产品分析总结")
        
        if 'price' in df.columns:
            df['price'] = pd.to_numeric(df['price'], errors='coerce')
            price_mean = df['price'].mean()
            price_median = df['price'].median()
            self.logger.info(f"平均价格: ${price_mean:.2f}")
            self.logger.info(f"价格中位数: ${price_median:.2f}")
        
        if 'rating' in df.columns:
            df['rating'] = pd.to_numeric(df['rating'], errors='coerce')
            rating_mean = df['rating'].mean()
            self.logger.info(f"平均评分: {rating_mean:.2f}/5.0")
        
        if 'reviews' in df.columns:
            df['reviews'] = pd.to_numeric(df['reviews'], errors='coerce')
            reviews_mean = df['reviews'].mean()
            self.logger.info(f"平均评论数: {reviews_mean:.0f}")
        
        # 生成PDF和Excel报告
        self.generate_pdf_report()
        self.generate_excel_report(df)
        
        self.logger.info(f"\n报告生成完成，保存在: {self.report_dir}")
        return True

 